import numpy as np
import pandas as pd

from common.handleExcel import format_datecolumn, save_to_excel, merge_same_column, save_to_newexcel_bytime
from common.part1GetList import get_list
from common.statisticsUtil import sum_by_column, size_by_column, fillna_with_zero
from ui import SelectExcelFileSheet


#if __name__ == '__main__':
def part1_process_data(selectExcelFileSheet: SelectExcelFileSheet):
    #url = r'E:\资料\省自然资源厅\1.example.xlsx'

    # 获取行政区划代码对应
    url4CountyCode2CountyNameCity,sheetName4CountyCode2CountyNameCity \
        = selectExcelFileSheet.showWindow('行政区划代码对应')
    #print(url4CountyCode2CountyNameCity)
    #print(sheetName4CountyCode2CountyNameCity)
    if url4CountyCode2CountyNameCity is None or sheetName4CountyCode2CountyNameCity is None:
        return



    # 获取基数清单
    url4List, sheetName4List = selectExcelFileSheet.showWindow('基数清单')
    #print(url4List)
    #print(sheetName4List)
    if url4List is None or sheetName4List is None:
        return
    baseListTable = get_list(url4CountyCode2CountyNameCity,sheetName4CountyCode2CountyNameCity,
                             url4List,sheetName4List)
    # 格式化日期列
    baseListTable = format_datecolumn(baseListTable,
                                     ['约定（变更）动工时间', '实际动工时间', '实际竣工时间'])
    #print('获取基数清单 :baseListTable')
    #print(baseListTable)



    # 获取已经处置清单
    url4List, sheetName4List = selectExcelFileSheet.showWindow('已处置清单')
    if url4List is None or sheetName4List is None:
        return
    handledListTable = get_list(url4CountyCode2CountyNameCity, sheetName4CountyCode2CountyNameCity,
                                url4List, sheetName4List)
    # 格式化日期列
    handledListTable = format_datecolumn(handledListTable,
                                      ['约定（变更）动工时间', '实际动工时间','实际竣工时间', '实际竣工时间'])
    #print('已经处置清单 handledListTable')
    #print(handledListTable)




    #获取年内新增清单
    url4List, sheetName4List = selectExcelFileSheet.showWindow('年内新增清单')
    if url4List is None or sheetName4List is None:
        return
    newYearListTable = get_list(url4CountyCode2CountyNameCity, sheetName4CountyCode2CountyNameCity,
                                url4List, sheetName4List)
    # 格式化日期列
    newYearListTable = format_datecolumn(newYearListTable,
                                         ['签订日期','约定动工时间','闲置产生时间','闲置处置时间'])
    #print('内新增清单 newYearListTable')
    #print(newYearListTable)

    # 对基数清单 根据'设区市', '实际管理区' 分组求和
    # 根据'设区市', '实际管理区' 分组求和结果再按照设区市分组求和
    # column_names4group  ['设区市', '实际管理区']为分组的列
    # column_name4value  '供应面积'为求和的列
    # column_NewName4value  '基数'为求和结果列
    baseListTableGroupSum = sum_by_column(baseListTable,
                                              ['设区市', '实际管理区'],
                                              '供应面积',
                                            '基数')
    baseListTableGroupSum['基数'] = baseListTableGroupSum['基数'] *15
    #计算省定任务
    baseListTableGroupSum['省定任务'] = baseListTableGroupSum['基数'] * 0.15

    # 对已经处置清单 根据'设区市', '实际管理区' 分组求和
    # 根据'设区市', '实际管理区' 分组求和结果再按照'设区市'分组求和
    # column_names4group  ['设区市', '实际管理区']为分组的列
    # column_name4value  '供应面积'为求和的列
    # column_NewName4value  '已处置面积'为求和结果列
    handledListTableGroupSum = sum_by_column(handledListTable,
                                          ['设区市', '实际管理区'],
                                          '供应面积',
                                             '已处置面积')
    handledListTableGroupSum['已处置面积'] = handledListTableGroupSum['已处置面积'] * 15

    # 对年内新增清单 根据'设区市', '实际管理区' 分组求和
    # 根据'设区市', '实际管理区' 分组求和结果再按照'设区市'分组求和
    # column_names4group  ['设区市', '实际管理区']为分组的列
    # column_name4value  '供应面积'为求和的列
    # column_NewName4value  '年内新增闲置土地面积'为求和结果列
    newYearListTable4GroupSum = sum_by_column(newYearListTable,
                                              ['设区市', '实际管理区'],
                                              '供应面积',
                                              '年内新增闲置土地面积')
    newYearListTable4GroupSum['年内新增闲置土地面积'] = newYearListTable4GroupSum['年内新增闲置土地面积'] * 15

    # 基数清单和已经处置清单 统计合并
    # ['设区市', '实际管理区']合并条件列
    # how='outer'外连接，返回基数清单和已经处置清单所有的列（不管有没有匹配上）
    result4BaseHandledListMerge = pd.merge(baseListTableGroupSum,
                                           handledListTableGroupSum,
                                           on=['设区市', '实际管理区'],
                                           how='outer')
    result4BaseHandledListMerge = result4BaseHandledListMerge.sort_values(['设区市', '实际管理区'])
    #print('基数清单和已经处置清单 统计合并:result4basehandledListMerge')
    #print(result4BaseHandledListMerge)

    # 基数清单和已经处置清单统计合并结果与内年新增清单 统计合并
    # ['设区市', '实际管理区']合并条件列
    # how='outer'外连接，返回基数清单和已经处置清单统计合并结果与内年新增清单所有的列（不管有没有匹配上）
    result4BaseHandlednewYearListMerge = pd.merge(result4BaseHandledListMerge,
                                                  newYearListTable4GroupSum,
                                                  on=['设区市', '实际管理区'],
                                                  how='outer')
    result4BaseHandlednewYearListMerge = result4BaseHandlednewYearListMerge.sort_values(['设区市', '实际管理区'])

    # 填充列值为NaN填充为0
    result4BaseHandlednewYearListMerge = fillna_with_zero(result4BaseHandlednewYearListMerge, '基数')
    result4BaseHandlednewYearListMerge = fillna_with_zero(result4BaseHandlednewYearListMerge, '省定任务')
    result4BaseHandlednewYearListMerge = fillna_with_zero(result4BaseHandlednewYearListMerge, '已处置面积')
    result4BaseHandlednewYearListMerge = fillna_with_zero(result4BaseHandlednewYearListMerge, '年内新增闲置土地面积')


    result4BaseHandlednewYearListMerge['年度净处置面积'] = result4BaseHandlednewYearListMerge['已处置面积'] - result4BaseHandlednewYearListMerge['年内新增闲置土地面积']
    result4BaseHandlednewYearListMerge['年度净处置率'] = np.where(
        result4BaseHandlednewYearListMerge['基数'] != 0,
        result4BaseHandlednewYearListMerge['年度净处置面积'] / result4BaseHandlednewYearListMerge['基数'],
        0)
    #result4basehandlednewYearListMerge['年度净处置面积']/result4basehandlednewYearListMerge['基数']
    result4BaseHandlednewYearListMerge['省定任务完成率'] = np.where(
       result4BaseHandlednewYearListMerge['省定任务'] != 0,
       result4BaseHandlednewYearListMerge['年度净处置面积'] / result4BaseHandlednewYearListMerge['省定任务'],
       0)
    #result4basehandlednewYearListMerge['年度净处置面积'] / result4basehandlednewYearListMerge['省定任务']
    result4BaseHandlednewYearListMerge['未完成任务数'] = result4BaseHandlednewYearListMerge['省定任务'] - result4BaseHandlednewYearListMerge['年度净处置面积']

    #('result4basehandlednewYearListMerge')
    #print(result4BaseHandlednewYearListMerge)


    #保存

    #save_sheet_name = save_to_excel(result4BaseHandlednewYearListMerge, url4List)
    url4Result,new_sheet_name = save_to_newexcel_bytime(result4BaseHandlednewYearListMerge, '1.闲置周报表格-闲置成果表')
    #print(save_sheet_name)
    #合并相同列
    merge_same_column(url4Result, new_sheet_name)